package com.wan.dao.provider;

import com.wan.dao.BaseDao;
import com.wan.pojo.Provider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProviderDaoImpl implements ProviderDao{
    @Override
    public int proCount(Connection conn, String proCode, String proName) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int count = 0;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("select count(1) as count from smbms_provider ");
            if (proCode != null) {
                sql.append(" where proCode = ? ");
                list.add(proCode);
            }
            if (proCode != null & proName != null) {
                sql.append(" and proName = ? ");
                list.add(proName);
            }else if ( proCode == null & proName != null ){
                sql.append(" where proName = ? ");
                list.add(proName);
            }
        }
        System.out.println("proCount-->sql-->"+sql.toString());
        System.out.println("proCount-->proCode-->"+proCode);
        System.out.println("proCount-->proName-->"+proName);
        Object[] params = list.toArray();
        try {
            rs = BaseDao.execute(conn, pstm, rs, sql.toString(), params);
            while (rs.next()) {
                count = rs.getInt("count");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        System.out.println("proCount-->count-->"+count);
        return count;
    }

    @Override
    public List<Provider> getProviderList(Connection conn, String proCode, String proName, int currentPageNo, int pageSize) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        ArrayList<Provider> providersList = null;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("select * from smbms_provider ");

            if (proCode != null) {
                sql.append(" where proCode = ? ");
                list.add(proCode);
            }
            if (proCode != null & proName != null) {
                sql.append(" and proName = ? ");
                list.add(proName);
            }else if ( proCode == null & proName != null ){
                sql.append(" where proName = ? ");
                list.add(proName);
            }
            //根据输入的当前页码和页面大小计算分页
            sql.append(" order by creationDate DESC limit ?,?");
            System.out.println("getProviderList--list-->"+list);
            System.out.println("getProviderList--sql-->"+sql.toString());
            System.out.println("getProviderList--currentPageNo-->"+currentPageNo);
            System.out.println("getProviderList--pageSize-->"+pageSize);
            System.out.println("---------------------------------------");
            currentPageNo = (currentPageNo - 1)*pageSize;
            list.add(currentPageNo);
            list.add(pageSize);
        }

        Object[] params = list.toArray();

        try {
            providersList = new ArrayList<Provider>();
            rs = BaseDao.execute(conn, pstm, rs, sql.toString(), params);
            while (rs.next()) {
                Provider provider = new Provider();

                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreateBy(rs.getInt("createdBy"));
                provider.setCreationDate(rs.getDate("creationDate"));
                provider.setModifyBy(rs.getInt("modifyBy"));
                provider.setModifyDate(rs.getDate("modifyDate"));

                providersList.add(provider);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        System.out.println("getProviderList--providersList-->"+providersList.size());

        return providersList;
    }

    @Override
    public List<Provider> getProviderList_(Connection conn) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        ArrayList<Provider> providersList = null;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("select * from smbms_provider ");
        }

        Object[] params = list.toArray();

        try {
            providersList = new ArrayList<Provider>();
            rs = BaseDao.execute(conn, pstm, rs, sql.toString(), params);
            while (rs.next()) {
                Provider provider = new Provider();

                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreateBy(rs.getInt("createdBy"));
                provider.setCreationDate(rs.getDate("creationDate"));
                provider.setModifyBy(rs.getInt("modifyBy"));
                provider.setModifyDate(rs.getDate("modifyDate"));

                providersList.add(provider);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        System.out.println("getProviderList--providersList-->"+providersList.size());

        return providersList;
    }

    @Override
    public int addProvider(Connection conn, Provider provider, int createdBy) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int impact = 0;

        if (conn != null) {
            list = new ArrayList<Object>();
            sql = new StringBuffer();
            sql.append("insert into smbms_provider(proCode,proName,proContact,proPhone,proAddress,proFax,proDesc,createdBy,creationDate) values(?,?,?,?");
            // 四个必填项
            list.add(provider.getProCode());
            list.add(provider.getProName());
            list.add(provider.getProContact());
            list.add(provider.getProPhone());
            // 三个可以为空的项
            if(provider.getProAddress() != null){
                sql.append(",?");
                list.add(provider.getProAddress());
            }
            if(provider.getProFax() != null){
                sql.append(",?");
                list.add(provider.getProFax());
            }
            if(provider.getProDesc() != null){
                sql.append(",?");
                list.add(provider.getProDesc());
            }
            if(createdBy != 0){
                sql.append(",?");
                list.add(createdBy);
            }
            if(provider.getCreationDate() != null){
                sql.append(",?");
                list.add(provider.getCreationDate());
            }
            sql.append(")");
            System.out.println("addProvider--sql--"+sql.toString());
            Object[] params = list.toArray();

            try {
                impact = BaseDao.executeUpdate(conn,pstm,params,sql.toString());
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return impact;
    }

    @Override
    public int deleteProvider(Connection conn, int id) {

        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int impactCount = 0;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("delete from smbms_provider where id = ?");
            if (id != -1) {
                list.add(id);
            }else {
                list.add(null);
            }
        }
        Object[] params = list.toArray();
        try {
            impactCount = BaseDao.executeUpdate(conn,pstm,params,sql.toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return impactCount;
    }

    @Override
    public Provider getProviderById(Connection conn, int id) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        Provider provider = null;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("select * from smbms_provider where id = ?");
            if (id != -1) {
                list.add(id);
            }
        }
        Object[] params = list.toArray();
        try {
            rs = BaseDao.execute(conn, pstm, rs, sql.toString(), params);
            while (rs.next()) {
                provider = new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreateBy(rs.getInt("createdBy"));
                provider.setCreationDate(rs.getDate("creationDate"));
                provider.setModifyDate(rs.getDate("modifyDate"));
                provider.setModifyBy(rs.getInt("modifyBy"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }
        return provider;
    }

    @Override
    public int updateProvider(Connection conn, Provider provider,int modifyBy) {
        PreparedStatement pstm = null;
        ResultSet rs = null;
        StringBuffer sql = null;
        ArrayList<Object> list = null;
        int impactCount = 0;

        if (conn != null) {
            sql = new StringBuffer();
            list = new ArrayList<Object>();
            sql.append("update smbms_provider set ");

            if (provider.getProName() != null) {
                sql.append("proName = ? ,");
                list.add(provider.getProName());
            }
            if (provider.getProContact() != null) {
                sql.append("proContact = ? ,");
                list.add(provider.getProContact());
            }
            if (provider.getProPhone() != null) {
                sql.append("proPhone = ? ,");
                list.add(provider.getProPhone());
            }
            if (provider.getProAddress() != null) {
                sql.append("proAddress = ? ,");
                list.add(provider.getProAddress());
            }
            if (provider.getProFax() != null) {
                sql.append("proFax = ? ,");
                list.add(provider.getProFax());
            }
            if (provider.getProDesc() != null) {
                sql.append("proDesc = ? ,");
                list.add(provider.getProDesc());
            }
            if (modifyBy != 0) {
                sql.append(" modifyBy = ? ,");
                list.add(modifyBy);
            }
            if (provider.getModifyDate() != null) {
                sql.append(" modifyDate = ? ");
                list.add(provider.getModifyDate());
            }
            sql.append(" where id = ?");
            list.add(provider.getId());
        }
        Object[] params = list.toArray();
        try {
            impactCount = BaseDao.executeUpdate(conn,pstm,params,sql.toString());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            BaseDao.closeResource(null,pstm,rs);
        }

        return impactCount;
    }


}
